package com.dp.service.companymanage;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.ChannelVo;
import com.dp.service.impl3.BaseImpl;
import com.dp.util.KeyGenerator;
import com.dp.vo.NewsGroup;
import com.dp.vo.NewsSnmsg;
import com.dp.vo.TAreainfo;
import com.dp.vo.TSpcode;

@Service
@Transactional
public class OrganizationServiceImpl extends BaseImpl {
	
	public List<TSpcode> findAllParenTSpcodeanization(TSpcode spcode){
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
		if(spcode != null && spcode.getSpcode()!=null)
			sqlS = new StringBuffer("SELECT n FROM TSpcode n where n.spcode not in " +
					"(select ts2.spcode from TSpcode ts2 where ts2.upspcode='"+spcode.getSpcode()+"')");
		List<TSpcode> queryList=null;
		try {
			Query query = em.createQuery(sqlS.toString());
			logger.info("findAllParenTSpcodeanization excute sql:"+sqlS.toString());
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error("findAllParenTSpcodeanization error!!!"+e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}
	
	
	public int inserTSpcodeanization(TSpcode organization)
	{
		//0成功1失败2已存在3同级下名称存在
		int t = 0;
//		String spcode = this.ObjectToStringDB(organization.getSpcode());
		String spcode = this.ObjectToStringDB(KeyGenerator.getInstance().getNextKeyValue("t_orgspcode"));
		String spname = this.ObjectToStringDB(organization.getSpname());
		String spaddress = this.ObjectToStringDB(organization.getSpaddress());
		String sptelphone = this.ObjectToStringDB(organization.getSptelphone());
		String splinkman = this.ObjectToStringDB(organization.getSplinkman());
		String spwebaddress = this.ObjectToStringDB(organization.getSpwebaddress());
		String upspcode = this.ObjectToStringDB(organization.getUpspcode());
		String level = "0";
		try {
//			this.init();
			
			//(select (select level from t_spcode where spcode="+upspcode+")+1) 父级level+1  若父级level=null则结果为null
			//rs1=stmt.executeQuery("select (select level from t_spcode where spcode="+upspcode+")+1");
			sql="select (select level from t_spcode where spcode="+upspcode+")+1";
//			rs1.next();
//			if(rs1.getString(1) != null && !"null".equals(rs1.getString(1))) 
//			{
//				level = rs1.getString(1);
//			}
			level=this.getOneString();
			//sourceid
			int nowkey=0;
			//rs1=stmt.executeQuery("select key_value from main_key where key_name='t_source'");
			//while(rs1.next()) 
			nowkey=Integer.parseInt(KeyGenerator.getInstance().getNextKeyValue("t_source"));
			String temp="0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
			int f=nowkey/temp.length();
			int l=nowkey % temp.length();				
			String sourceid=this.ObjectToStringDB(temp.substring(f,f+1)+temp.substring(l,l+1));
			//如果是根,则先插表 后用last_indentity更新,否则select id作为parentid
			if(level.equals("0"))
			{
				//检查同一父级下是否存在同名 BINARY 区分大小写
				sql=("select count(spcode) as totalrow from t_spcode where spname='BINARY "+spname+"' and level=0");
//				rs1=stmt.executeQuery("select count(spcode) as totalrow from t_spcode where spname='BINARY "+spname+"' and level=0");
//				rs1.next();
//				
				if(!"0".equals(this.getOneString())) 
				{
					t= 3;
					return t;
				}
				
				this.sql = "insert into t_spcode(spcode,spname,spaddress,sptelphone,splinkman,spwebaddress,sourceid,upspcode,level)" +
						"values("+spcode+","+spname+","+spaddress+","+sptelphone+","+splinkman+","+spwebaddress+","+sourceid+"," +
						upspcode+","+level + ")";
				logger.info("inserTSpcodeanization excute sql1:"+sql);
				
				this.em.createNativeQuery(sql).executeUpdate();
				this.sql = "update t_spcode set parentid=(select LAST_INSERT_ID()) where spcode="+spcode;
				logger.info("inserTSpcodeanization excute sql1:"+sql);
				this.em.createNativeQuery(sql).executeUpdate();
			}
			else
			{
				//检查同一父级下是否存在同名 
				sql=("select count(spcode) as totalrow from t_spcode where spname=BINARY "+spname+" and upspcode="+upspcode);
			//	rs1=stmt.executeQuery("select count(spcode) as totalrow from t_spcode where spname=BINARY "+spname+" and upspcode="+upspcode);
				//rs1.next();
				if(!"0".equals(this.getOneString())) 
				{
					t= 3;
					return t;
				}
				this.sql = "insert into t_spcode(spcode,spname,spaddress,sptelphone,splinkman,spwebaddress,sourceid,upspcode,level,parentid)"
				+"values("+spcode+","+spname+","+spaddress+","+sptelphone+","+splinkman+","+spwebaddress+","+sourceid+","+upspcode+","+level+
				",(select id from t_spcode t1 where t1.spcode="+upspcode+"))";
				logger.info("inserTSpcodeanization excute sql1:"+sql);
				this.em.createNativeQuery(sql).executeUpdate();
			}
			this.sql ="insert into news_group(grouprulename,grouptype,note,userid,createtime) values ("+spname+",2,'创建机构时生成的同名的自定义组，不能修改。','root',now())";
			logger.info("inserTSpcodeanization excute sql2:"+sql);
			this.em.createNativeQuery(sql).executeUpdate();
			this.sql ="insert into t_spcode_device(groupid,spcode) values ((select LAST_INSERT_ID()),"+spcode+")";
			logger.info("inserTSpcodeanization excute sql3:"+sql);
			this.em.createNativeQuery(sql).executeUpdate();
		
		} catch (Exception e) {
			t=1;
			logger.error("inserTSpcodeanization fail !!!" + e.getMessage());
			e.printStackTrace();
		
		}
		
		return t;
	}
	
	public TSpcode geTSpcodeanizationByCode(String spcode)
	{
		TSpcode t = null;
		try {
			t = this.em.find(TSpcode.class, spcode);
		}catch (Exception e) {
			logger.error("geTSpcodeanizationByCode error!!!"+e.getMessage());
		}
		return t;
	}
	
	public int updateOrganization(TSpcode organization)
	{
		//0成功1失败3同级下名称存在
		int t = 0;
		String spcode = this.ObjectToStringDB(organization.getSpcode());
		String spname = this.ObjectToStringDB(organization.getSpname());
		String spaddress = this.ObjectToStringDB(organization.getSpaddress());
		String sptelphone = this.ObjectToStringDB(organization.getSptelphone());
		String splinkman = this.ObjectToStringDB(organization.getSplinkman());
		String spwebaddress = this.ObjectToStringDB(organization.getSpwebaddress());
		String upspcode = this.ObjectToStringDB(organization.getUpspcode());
		String level = "0";
		try {
//			sql=("select (select level from t_spcode where spcode="+upspcode+")+1");
			sql=("select level from t_spcode where spcode='"+upspcode+"'");
		     level=this.getOneString();	
		     sql="select id from t_spcode t1 where t1.spcode="+spcode;
		    int idd=this.getOneInt(); 
			if(level.equals("0"))
			{
				//检查同一父级下是否存在同名
				//sql=("select count(spcode) as totalrow from t_spcode where spname=BINARY "+spname+" and level=0 and spcode!="+spcode);
				sql=("select count(spcode) as totalrow from t_spcode where spname='"+spname+"' and level=0 and spcode!="+spcode);
				if(!"0".equals(this.getOneString())) 
				{
					t= 3;
					return t;
				}
				
				
				this.sql ="update t_spcode "+//,(select (select id from t_spcode t1 where t1.spcode="+spcode+") a) as l " +
						"set spname="+spname+",spaddress="+spaddress+",sptelphone="+sptelphone+",splinkman="+splinkman+
						",spwebaddress="+spwebaddress+",upspcode="+upspcode+",level="+level+
						",parentid="+idd+" where spcode="+spcode;
				logger.info("updateOrganization excute sql1:"+sql);
				this.em.createNativeQuery(sql).executeUpdate();
			}
			else
			{
				//检查同一父级下是否存在同名
				sql=("select count(spcode) as totalrow from t_spcode where spname="+spname+" and upspcode="+upspcode+" and spcode!="+spcode);
				
				if(!"0".equals(this.getOneString())) 
				{
					t= 3;
					return t;
				}
				this.sql ="update t_spcode "+//,(select (select id from t_spcode t1 where t1.spcode="+upspcode+") a) as l " +
						"set spname="+spname+",spaddress="+spaddress+",sptelphone="+sptelphone+",splinkman="+splinkman+
						",spwebaddress="+spwebaddress+",upspcode="+upspcode+",level="+level+
						",parentid="+idd+" where spcode="+spcode;
				logger.info("updateOrganization excute sql1:"+sql);
				this.em.createNativeQuery(sql).executeUpdate();
			}
//			this.sql ="update t_spcode,(select (select level from t_spcode t2 where t2.spcode="+upspcode+")+1 a) as l set spname="+spname+
//				",spaddress="+spaddress+",sptelphone="+sptelphone+",splinkman="+splinkman+",spwebaddress="+spwebaddress+",upspcode="
//				+upspcode+",level=l.a where spcode="+spcode;
//			logger.info("updateOrganization excute sql1:"+sql);
//			stmt.addBatch(sql);
			this.sql ="update news_group set grouprulename="+spname+
					" where groupruleid=(select groupid from t_spcode_device where spcode="+spcode+")";
			logger.info("updateOrganization excute sql2:"+sql);
			this.em.createNativeQuery(sql).executeUpdate();
		} catch (Exception e) {
			t=1;
			logger.error("updateOrganization fail !!!" + e.getMessage());
			e.printStackTrace();
	 }
		return t;
	}
	
//	private String ObjectToStringDB(Object obj) {
//        try {
//            String str = obj.toString().trim();
//            if (str.equals("") || str.equals("null")) {
//                return "null";
//            }
//            return "'" + str + "'";
//        } catch (Exception e) {
//            return "null";
//        }
//    }

	/**
	 * 一级栏目列表
	 * @return
	 */
	public List<ChannelVo> findAllfirstChannelList(){
		List<ChannelVo> channellist=new ArrayList<ChannelVo>();
		ChannelVo cv=null;
		try {
	       
			this.sql="SELECT tc.channel_id,tc.channel_name,tc.function_name,tc.channel_type,tc.level,tc.previous_id,(case when nf.service_code is null then tc.service_code else nf.service_code end ) as service_code FROM t_channels tc left join news_extends_firstchannel nf on nf.channel_id=tc.channel_id " +
			" where primal_id =(select channel_id from t_channels where channel_name='news' and status='ENABLED')  and  previous_id =(select channel_id from t_channels where channel_name='news' and status='ENABLED') and level != 1 ";
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:temp)
		{
				cv=new ChannelVo();
				cv.setChannel_id(this.getString(o[0]));
				cv.setChannelName(this.getString(o[1]));
				channellist.add(cv);
			}
			
		} catch (Exception e) {
			logger.error("findAllfirstChannelList fail !!!" + e.getMessage());
		
		}
		return channellist;
	} 
	
	/**
	 * 得到机构的映射的一级栏目对象
	 * @param spcode
	 * @return
	 */
	public List<ChannelVo> findChannelBySpcode(String spcode) {
		List<ChannelVo> cvlist=new ArrayList<ChannelVo>();
		try {
	       
			this.sql="select tc.channel_id,tc.channel_name from t_spcode_channel tsc,t_channels tc where  tsc.channelid=tc.channel_id and spcode='"+spcode+"'";
			//rs2=stmt.executeQuery(sql);
			ChannelVo cv=null;
			List<Object[]> temp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:temp)
			{
					cv=new ChannelVo();
					cv.setChannel_id(this.getString(o[0]));
					cv.setChannelName(this.getString(o[1]));
				cvlist.add(cv);
			}
		//	conn.commit(); 
		} catch (Exception e) {
			logger.error("findChannelBySpcode fail !!!" + e.getMessage());
			e.printStackTrace();
			
		}
		return cvlist;
	}
	
	/**
	 * 分配一级栏目
	 * @param organization
	 * @param channel
	 * @return
	 */
	public boolean assignChannel(TSpcode organization, String channelids) {
		Boolean resultb=true;
		try {
			//this.init();
			this.sql="delete from t_spcode_channel where spcode='"+organization.getSpcode()+"'";
		    this.em.createNativeQuery(sql).executeUpdate();
			
			this.sql="insert into t_spcode_channel (spcode, channelid) values (?,?)";
			//prep1=conn.prepareStatement(sql);
			if(channelids!=null&&!"".equals(channelids)){
				Query	query=em.createNativeQuery(sql);
				String[] chanleid_arys=channelids.split(",");
				for(String channelid:chanleid_arys){
//					prep1.setString(1, organization.getSpcode());
//					prep1.setString(2, channelid);
//					prep1.addBatch();
					query.setParameter(1,  organization.getSpcode());
					query.setParameter(2, channelid);
					query.executeUpdate();
				}
			}
			
		} catch (Exception e) {
			logger.error("assignChannel fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			
		}
		return resultb;
	}

	/**
	 * 获得所有用户组对象
	 * @return
	 */
	public List<NewsGroup> findAllNewsGroupEntity() {
		List<NewsGroup> queryList=new ArrayList<NewsGroup>();
		StringBuffer sqlS = new StringBuffer("SELECT n FROM NewsGroup n WHERE 1=1 order by n.grouptype");
		try {
			Query query = em.createQuery(sqlS.toString());
			queryList = query.getResultList();
		} catch (Exception e) {
			logger.error(e.getMessage());
			e.printStackTrace();
		}
		return queryList;
	}

	/**
	 * 机构已关联用户组对象集合
	 * @param spcode
	 * @return
	 */
	public List<NewsGroup> getSelectedgroupListbySpcode(String spcode) {
		List<NewsGroup> groupllist=new ArrayList<NewsGroup>();
		NewsGroup group=null;
		TAreainfo area=null;
		try {
	       
			this.sql="select ng.groupruleid,ng.grouprulename,ng.mlocale,(select areaname from t_areainfo where areaid=ng.marea) areaname from news_group ng where ng.groupruleid in (select rulegroup from t_spcode_rulegroup where spcode='"+spcode+"')";
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				group=new NewsGroup();
				group.setGroupruleid(this.getInteger(o[0]));
				group.setGrouprulename(this.getString(o[1]));
				group.setMlocale(this.getString(o[2]));
				area=new TAreainfo();
				area.setAreaname(this.getString(o[3]));
				group.setMarea(area);
				groupllist.add(group);
			}
			
		} catch (Exception e) {
			logger.error("getSelectedgroupListbySpcode fail !!!" + e.getMessage());
			e.printStackTrace();
			
		}
		return groupllist;
	}

	/**
	 * 关联用户组
	 * @param organization
	 * @param groupids
	 * @return
	 */
	public boolean relationgroup(TSpcode organization, String groupids) {
		boolean resultb=true;
		try {
			//this.init();
			this.sql="delete FROM t_spcode_rulegroup WHERE spcode='"+organization.getSpcode()+"'" ;
			//this.prep=this.conn.prepareStatement(sql);	
			this.em.createNativeQuery(sql).executeUpdate();
			this.sql="insert into t_spcode_rulegroup (spcode,rulegroup) values (?,?)";
//			this.emr.createNativeQuery(sql).executeUpdate();
			//删除老的组织机构和用户组的关系
//			this.prep.setString(1, organization.getSpcode());
//			prep.executeUpdate();
			
			if(groupids!=null&&!"".equals(groupids)){
				
				Query	query=em.createNativeQuery(sql);
				
				String[] groupids_arys=groupids.split(",");
				//增加新的组织机构和用户组的关系
				for (int i = 0; i < groupids_arys.length; i++) {
					int groupid = Integer.parseInt(groupids_arys [i]) ;
//					prep1.setString(1, organization.getSpcode());
//					prep1.setInt(2, groupid);
//					prep1.addBatch();

					query.setParameter(1,  organization.getSpcode());
					query.setParameter(2, groupid);
					query.executeUpdate();
				}
//				prep1.executeBatch();
			}
			
//			conn.commit(); 
		} catch (Exception e) {
			logger.info("relationgroup fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		}
		return resultb;
	}
	
	public List<TSpcode> findAllParenTSpcodeanizationBySpcode(String spcode){
		StringBuffer sqlS = new StringBuffer("SELECT n FROM TSpcode n ");
		if(spcode != null && spcode.length()>0)
		{
			sqlS = new StringBuffer("SELECT n FROM TSpcode n where n.spcode in ("+spcode+") ");
		}
		sqlS.append(" order by n.id ");
		List<TSpcode> queryList=null;
		try {
			
			logger.info("findAllParenTSpcodeanizationBySpcode excute sql----:"+sqlS.toString());
			queryList=this.em.createQuery(sqlS.toString()).getResultList();
//			ArrayList result = this.queryALLResultSet(sqlS.toString());
//			if(result != null && result.size()>0)
//			{
//				queryList = new ArrayList<TSpcode>(result.size());
//				for(int i=0;i<result.size();i++)
//				{
//					HashMap tmp = (HashMap)result.get(i);
//					TSpcode one = new TSpcode();
////					one.setId(Integer.parseInt((String)tmp.get("id")));
//					one.setLevel(Integer.parseInt((String)tmp.get("level")));
////					one.setParentid(Integer.parseInt((String)tmp.get("parentid")));
////					one.setSourceid((String)tmp.get("sourceid"));
////					one.setSpaddress((String)tmp.get("spaddress"));
////					one.setSplinkman((String)tmp.get("splinkman"));
//					one.setSpcode((String)tmp.get("spcode"));
//					one.setSpname((String)tmp.get("spname"));
////					one.setSptelphone((String)tmp.get("sptelphone"));
////					one.setSpwebaddress((String)tmp.get("spwebaddress"));
//					one.setUpspcode((String)tmp.get("upspcode"));
//					queryList.add(one);
//				}
//			}
		} catch (Exception e) {
			logger.error("findAllParenTSpcodeanizationBySpcode error!!!"+e.getMessage());
			e.printStackTrace();
		}finally{
			//this.closeAll();
		}
		return queryList;
	}
}
